package com.qf.studentmanager.dao;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.qf.studentmanager.model.Course;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;

public class CourseDao {
    ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();

    QueryRunner queryRunner = new QueryRunner(comboPooledDataSource);

    public List<Course> getCourses() {
//        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
//        QueryRunner queryRunner = new QueryRunner(comboPooledDataSource);
        String sql = "select  * from course";
        List<Course> list = null;
        try {
            list = queryRunner.query(sql, new BeanListHandler<>(Course.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public Course getCourseByName(String courseName){ //查询单个课程看有无重名
        String sql = "select  * from course where courseName = ?;";
        Course course = null;
        try {
            course = queryRunner.query(sql, new BeanHandler<>(Course.class), courseName);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return course;
    }

    public Integer addCourse(Course course){
        String sql = "insert into course set courseName=?";
        int i = 0;
        try {
            i = queryRunner.update(sql, course.getCourseName());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    public Integer deleteCourse(int cid ) throws SQLException {
        String sql = "delete from  course where  cid =?;";
        int i = queryRunner.update(sql, cid);
        return i;
    }

    public List<Course> getCoursesByGid(String gid) {
        try {
            return queryRunner.query("select * from course Inner join grade_course gc on course.cid = gc.cid where  gid = ?", new BeanListHandler<>(Course.class),gid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

}
